#!/usr/bin/perl
use Data::Dumper; 
use Getopt::Std; 

my %options = ();
getopts("shq", \%options); 

sub trim{
  my $string = shift;
  $string =~ s/^\s+//;
  $string =~ s/\s+$//;
  return $string;
}

sub trim_split{
  my $split = shift; 
  my $string = shift; 
  return map {trim($_)} split($split, trim($string));
}

sub is_numeric {
  use warnings FATAL => qw( numeric uninitialized );
  local $@;
  my $x = shift;
  return eval { $x == $x };
}

sub get_value {
  my $val = shift;
  return is_numeric($val) ? $val : "\"$val\"";
}

sub parse_sql{
  my $statement = shift;
  $statement =~ s/(separator|fields|select|from|where|order|limit|format|widths)/\~$1/g;  
  $statement =~ s/(^\~)|\[|\]//g; #replace ALL leading ~ and extra [ ] or ,
  $statement = "where $statement" if(index($statement, '~') == -1 && $statement); 
  my %keywords = (); 
  for (split(/\~/, $statement)){
    my @parts = trim_split(' ', $_);
    %keywords->{shift @parts} = \@parts;
  }

  if($keywords{widths}){
    my %widths = ();
    while(@{$keywords{widths}}){
      my $key = shift @{$keywords{widths}}; 
      my $val = shift @{$keywords{widths}};
      %widths->{$key} = $val if ($key && $val);
    }
    %keywords->{widths} = \%widths;
  }
  return \%keywords; 
}

sub get_table_columns{
  my $table = shift;
  my $separator = shift;
  my %colmap = ();
  my $row = qx(cat $table | head -1);
  $row =~ s/\"|\'//g;
  my @tbl_headers = trim_split("\\$separator", $row);
  my $ci = 0;
  for(map {trim($_)} @tbl_headers){
    %colmap->{$_} = ++$ci;
  }
  return (\@tbl_headers, \%colmap);
}

#comparison operator map 
my $commap = {'gt' => '>', 'lt' => '<', 'eq' => '=='}; 
my $sepmap = {'space' => ' ', 'comma' => ',', 'tab' => '\t', 'pipe' => '|'};
my $statement = parse_sql(join(' ', @ARGV)); 
#print Dumper $statement;

#default separator to pipe [|]
my $separator = $statement->{separator} ? shift @{$statement->{separator}} : 'pipe';
$separator = $sepmap->{$separator} if $sepmap->{$separator};

#if no table, assume STDIN
if($statement->{from}) {
  #handle multiple inputs for joins etc, for now just grab first
  $table = shift @{$statement->{from}};
} else {
  #generate unique tmp file..
  $table = '/tmp/' . trim(`uuidgen`);
  open TBL, ">$table" or die $!;
  while (<STDIN>) {print TBL $_}
  close TBL;
}

#generate column map [need to allow for fields clause to over-ride]
my ($tbl_headers, $colmap) = get_table_columns($table, $separator); 
#print Dumper $colmap; 
#print Dumper $tbl_headers; 

#build where clause[s] if provided
my @clauses = ();
if($statement->{where}){
  for (trim_split('and', join ' ', @{$statement->{where}})){
    my @parts = trim_split(' ', $_);
    my $field = shift @parts;
    my $comp = shift @parts;
    #capture remainder of values - this is nice for multiword strings or in clauses
    my $value = join(' ', @parts); 

    #is it an implicit join?
    if(index($field, '.') > 0 && index($value, '.') > 0){
      my($ptable, $pkey) = split('\.', $field);
      my($ftable, $fkey) = split('\.', $value); 
      #subquery 
      my @sclauses = ();
      for (my @recs = map {trim($_)} qx(./asql -h select $fkey from $ftable)){
        push @sclauses, "\$$colmap->{$pkey} $commap->{$comp} " . get_value($_);
      }
      push @clauses, '(' . (join '||', @sclauses) . ')' if @sclauses;
    }
    if($comp eq 'in'){  
      my @sclauses = (); 
      for (split ' ', $value){
        push @sclauses, "\$$colmap->{$field} == " . get_value($_);
      }
      push @clauses, '(' . (join '||', @sclauses) .')' if @sclauses; 
    } else {
      push @clauses, "\$$colmap->{$field} $commap->{$comp} " . get_value($value);
    }
  }
}
my $awkif = @clauses ? 'if('.join(' && ', @clauses).')' : '';

#get fields we are using, if none - assume all
my @headers = $statement->{select} ? @{$statement->{select}} : @{$tbl_headers};
my $self = (); 
my $selv = ();
my $hlist = ();
my $blist = ();
for (@headers){
  #determine col width based on type? - allow explict decleration in meta ?

  #is there an explicit width
  my $w = $statement->{widths} && $statement->{widths}->{$_} ? $statement->{widths}->{$_} : 10; 

  push @{$hlist}, sprintf('%-' . $w . 's', $_);
  push @{$blist}, '-' x $w; 
  push @{$self}, '%-' . $w . '.' . $w . 's'; 
  push @{$selv}, "\$$colmap->{$_}";
} 

$self = join('|', @{$self});
$selv = join(',', @{$selv});
$hlist = join('|', @{$hlist}); 
$blist = join('+', @{$blist});
$query = <<END;
tail +2 $table | awk 'BEGIN {FS = "$separator"} {$awkif printf("$self\\n", $selv)}' | sort -t "|" -k 1 -n
END

print $query if $options{q};
if($options{h}){
  print "$hlist\n";
} else { 
  print "$blist\n$hlist\n$blist\n";
}
print qx($query); 

#implement order by w/ sort, use -n to be numeric, use -r to reverse, -k 1 to indicate field 1, -t "|" to indicate seperator

#implement distinct w/ uniq

#implement joins and sub-selects
#use getline to grab lines from other files..? grepped with inner join?

#insert, update, delete

#implement max, min, sum, abs, div, add and other functions

#add ability to define how many rows are before and after the header thus
#------+------
#col 1 | col 2
#-------------
#select col1 header_padding 1 1 [before after]

#allow regex as comparison/filter operator
